/*********************************************************************
/* Java Anything Implementation as universal Data Access Object
/*
/* Copyright (C) 2006 Stefan Tramm
/* 
/* This library is free software; you can redistribute it and/or
/* modify it under the terms of the GNU Lesser General Public
/* License as published by the Free Software Foundation; either
/* version 2.1 of the License, or (at your option) any later version.
/* 
/* This library is distributed in the hope that it will be useful,
/* but WITHOUT ANY WARRANTY; without even the implied warranty of
/* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
/* Lesser General Public License for more details.
/* 
/* You should have received a copy of the GNU Lesser General Public
/* License along with this library; if not, write to the Free Software
/* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
/* MA  02110-1301  USA
/********************************************************************/

import static any.functor.Functions.asBigDecimal;
import static any.functor.Functions.asDate;
import static any.functor.Functions.asLong;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;

import any.Any;
import any.ImmutableAny;


public class AnyDAO {

    public static final ImmutableAny nullAny = Any.create.immutableAny();

    static private class ColDesc  {
        public String columnName;
        public int    columnType;
//        public String columnTypeName;
//        public String columnClassName;
        public ColDesc(ResultSetMetaData rsmd, int i) throws SQLException {
            this.columnName = rsmd.getColumnName(i);
            this.columnType = rsmd.getColumnType(i);
//            this.columnTypeName = rsmd.getColumnTypeName(i);
//            this.columnClassName = rsmd.getColumnClassName(i);
        }
//        public Any asAny() {
//        	Any a = Any.create.any();
//            a.put("columnName", columnName);
//            a.put("columnType", columnType);
//            a.put("columnTypeName", columnTypeName);
//            a.put("columnClassName", columnClassName);
//            return a;
//        }
    }
    
    public interface Callback {
        void init();
        Any finish(int rowCount);
        void call(int rowCount, Any row);
    }

    // if a column is requested several times, then only one is stored
    // in the final anything
    public static Any query(Connection con, String q) {
        return query(con, q, new AnyDAO.Callback() {
            private Any s;
            public void     init() {s = Any.create.any();}
            public Any finish(int rowCount) {return s;} 
            public void     call(int rc, Any row) {s.append(row);}
        });
    }

    public static Any query(Connection con, String q, Callback c) {
        Any any = Any.create.any();
        any.get("Stats").put("query", q);
        try {
            // create and execute a SELECT
            Timer t = new Timer();
            t.start();
            Statement stmt = con.createStatement();
            t.stop();
            any.get("Stats").put("create", t.getTime());
            
            t.start();
            ResultSet rs = stmt.executeQuery(q);
            t.stop();
            any.get("Stats").put("execute", t.getTime());

            // build result Statsdata
            t.start();
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            ColDesc[] col = new ColDesc[colCount]; // build this array for performance
            for (int i = 0; i < colCount; i++) {
                col[i] = new ColDesc(rsmd, i+1);
            }
            t.stop();
            any.get("Stats").put("readMeta", t.getTime());

            // traverse through results
            t.start();
            int rowCount = 0;
            c.init();
            while (rs.next()) {
                Any r = Any.create.any();
                String val = null;
                for (int i = 0; i < colCount; i++) {
                    val = rs.getString(i+1);
                    if (val == null) {
                        r.put(col[i].columnName, Any.create.any()); // null
                    } else {
                        switch (col[i].columnType) {
                            case Types.NUMERIC: // BigDecimal, 2
                                r.put(col[i].columnName, rs.getBigDecimal(i+1));
                                break;
                            case Types.TIMESTAMP: // Date, 91
                            case Types.DATE:
                            case Types.TIME:
                                r.put(col[i].columnName, rs.getDate(i+1));
                                break;
                            default:
                                r.put(col[i].columnName, val);
                        }
                    }
                }
                c.call(++rowCount, r);
            }
            any.put("Data", c.finish(rowCount));
            t.stop();
            any.get("Stats").put("readData", t.getTime());
                
            // close statement and connection
            t.start();
            stmt.close();
            t.stop();
            any.get("Stats").put("close", t.getTime())
                            .put("rowCount", rowCount)
                            .put("columnCount", colCount);

        } catch (java.lang.Exception ex) {
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            ex.printStackTrace(pw);
            Any e = any.get("Exception");
            e.put("Class", ex.getClass().getName());
            e.put("Message", ex.getMessage());
            e.put("StackTrace", sw.toString());
        }
        return any;
    }

    // execute an (updating) SQL statement (insert, update, etc)
    // request is an Anything containing
    //   query     -- Array of Strings (statement followed by attribut names)
    //   input     -- Array of Dictionary of Attribute values
    // Returns Anything
    //   Stats     -- statistics
    //   Exception -- only avail, when an exception occured
    //   Result    -- int return from the executeUpdate
    public static Any statement(Connection con, String[] query, Any input) {
        // assert: query.length >= 1
        input = toArray(input);

        Any any = Any.create.any();
        any.getPut("Stats").put("Statement", query[0]);
        try {
            // prepare and execute
            Timer t = new Timer();
            t.start();
            PreparedStatement stmt = con.prepareStatement(query[0]);
            t.stop();
            long ptime = t.getTime();
            long stime = 0;
            long etime = 0;
            int rc = 0;
            // now process every row
            for (int r = 0; r < input.size(); r++) {
                t.start();
                for (int i = 1; i < query.length; i++) {
                    // fetch from row r the i.th attribute mentioned in the query
                    ImmutableAny val = input.get(r).get(query[i]);
                    // log only the first row...
                    if (r == 0) any.get("Stats").get("Statement").append(val.asString("NULL"));
                    Class<?> type = val.getType();
                    if (type == null) {
                    	stmt.setNull(i, Types.NULL);
                    } else if (type.equals(BigDecimal.class)) {
                    	stmt.setBigDecimal(i, val.give(asBigDecimal()));
                    } else if (type.equals(Date.class)) {
                    	stmt.setTimestamp(i, new Timestamp(val.give(asDate()).getTime()));
                    } else if (type.equals(BigInteger.class) || type.equals(Boolean.class)) {
                    	stmt.setLong(i, val.give(asLong().withDefault(0)));
                    } else {
                    	stmt.setString(i, val.asString(""));
                    }
                }
                t.stop();
                stime += t.getTime();
    
                t.start();
                rc += stmt.executeUpdate();
                t.stop();
                etime += t.getTime();
            }
            // close statement and connection
            t.start();
            stmt.close();
            t.stop();
            any.get("Stats").put("prepare", ptime)
                            .put("set", stime)
                            .put("update", etime)
                            .put("close", t.getTime())
                            .put("inputRowCount", input.size())
                            .put("rowCount", rc);
            any.put("Result", rc);
        } catch (java.lang.Exception ex) {
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            ex.printStackTrace(pw);
            Any e = any.get("Exception");
            e.put("Class", ex.getClass().getName());
            e.put("Message", ex.getMessage());
            e.put("StackTrace", sw.toString());
        }
        return any;
    }

    // convenience method to insert one row into a table
    // should be easy to implement an insert multiple rows with a JDBC batchStatement
    // dont use this method for production code, SQL injection possible
    public static Any insertRows(Connection con, String table, Any rows) {
        rows = toArray(rows);
        String[] q = new String[rows.get(0).size()+1];
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ");
        sb.append(table);
        sb.append(" (");
        for (int i = 0; i < rows.get(0).size(); i++) {
            if (i > 0) sb.append(',');
            sb.append(rows.get(0).getKeyForIndex(i));
            q[i+1] = rows.get(0).getKeyForIndex(i);
        }
        sb.append(") VALUES (");
        for (int i = 0; i < rows.get(0).size(); i++) {
            if (i > 0) sb.append(',');
            sb.append('?');
        }
        sb.append(")");
        q[0] = sb.toString();
        return statement(con, q, rows);
    }
    
    // convert a dict into an array of dict, if not already so
    private static Any toArray(Any a) {
        if (!a.isSimpleType() && a.getKeyForIndex(0) != null) {
            // we have a dict, so put it into a new Any
        	Any n = Any.create.any();
            n.set(0, a);
            return n;
        }
        return a;
    }
}
